package util;

import user.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBC {
    static ResultSet resultSet = null;
    static PreparedStatement preparedStatement = null;

    public static Connection getConnection(String username, String password) throws Exception { //此方法，数据库的驱动，连接
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//注册驱动
        String url = "jdbc:sqlserver://localhost:1433;DatabaseName=currDesign";
        return DriverManager.getConnection(url, username, password);//建立与数据库的连接
    }

    public static List<Book> getBook(Connection connection) throws SQLException { //查询图书，返回list集合
        String sql = "select * from dbo.图书";
        List<Book> list = new ArrayList<>();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Book book = new Book(resultSet.getString("ISBN"), resultSet.getString("书名"), resultSet.getString("作者"), resultSet.getString("出版社名字"));
            list.add(book);
        }
        return list;
    }

    public static void addBook(Book book, Connection connection) throws SQLException {
        String sql = "insert into dbo.图书(ISBN,书名,作者,出版社名字)" + "values (" + book.getISBN() + ",'" + book.getName() + "','" + book.getAuthor() + "','" + book.getPress() + "')";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }

    public static List<Book> getBookByISBN(String ISBN, Connection connection) throws Exception {//根据传入的ISBN，查询图书信息
        List<Book> list = new ArrayList<>();
        String sql = "select * from dbo.图书 where ISBN like '%" + ISBN + "%'";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Book book = new Book(resultSet.getString("ISBN"), resultSet.getString("书名"), resultSet.getString("作者"), resultSet.getString("出版社名字"));
            list.add(book);
        }
        return list;
    }

    public static List<Book> getBookByName(String name, Connection connection) throws Exception {//根据传入的书名，查询图书信息
        List<Book> list = new ArrayList<>();
        String sql = "select * from dbo.图书 where 书名 like '%" + name + "%'";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Book book = new Book(resultSet.getString("ISBN"), resultSet.getString("书名"), resultSet.getString("作者"), resultSet.getString("出版社名字"));
            list.add(book);
        }
        return list;
    }

    public static void update(Book book, Connection connection) throws SQLException { //修改图书
        String sql = "update dbo.图书 set 书名 ='" + book.getName() + "',作者='" + book.getAuthor() + "',出版社名字='" + book.getPress() + "' where ISBN= " + book.getISBN();
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }

    public static void deleteBook(String ISBN, Connection connection) throws SQLException {
        String sql = "delete from dbo.图书 where ISBN=" + ISBN;
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }

    public static void release() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
    }
}
